/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.kallsonys.dao;

import com.kallsonys.business.KallSonysFacade;
import com.kallsonys.object.Supplier;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

/**
 *
 * @author allan
 */
public class SupplierDao {

    private static String sqlCreate = "{ call PKG_CRUDS.CREATE_SUPPLIER (?, ?) }";
    private static String sqlUpdate = "{ call PKG_CRUDS.UPDATE_SUPPLIER (?, ?) }";
    private static String sqlGetById = "{ call PKG_CRUDS.GET_SUPPLIER (?, ? ) }";
    private DataSource ds;
    private KallSonysFacade localFacade;

    public SupplierDao(KallSonysFacade localFacade) {
        this.ds = localFacade.getSharedDs();
        this.localFacade = localFacade;
    }

    /**
     * create
     * @param supplier
     * @return
     * @throws java.sql.SQLException
     */
    public int create(Supplier supplier) throws SQLException, Exception {
        CallableStatement stmt = null;
        Connection con = null;
        int respuesta = 0;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlCreate);

            stmt.setString(1, supplier.getName());
            stmt.registerOutParameter(2, OracleTypes.INTEGER);
            stmt.execute();

            respuesta = stmt.getInt(2);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
        return respuesta;
    }

    /**
     * update
     * @param country
     * @throws java.sql.SQLException
     */
    public void update(Supplier supplier) throws SQLException, Exception {
        CallableStatement stmt = null;
        Connection con = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlUpdate);

            stmt.setInt(1, supplier.getId());
            stmt.setString(2, supplier.getName());
            stmt.execute();

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }
    }

    /**
     * getById
     * @param idCountry
     * @return
     */
    public Supplier getById(int id) throws SQLException, Exception {
        Supplier retorno = null;

        CallableStatement stmt = null;
        Connection con = null;
        ResultSet rs = null;

        try {
            con = ds.getConnection();
            stmt = con.prepareCall(sqlGetById);

            stmt.setInt(1, id);
            stmt.registerOutParameter(2, OracleTypes.CURSOR);
            stmt.execute();

            Object o = stmt.getObject(2);
            if (o instanceof ResultSet) {
                rs = (ResultSet) o;

                if (rs.next()) {
                    retorno = new Supplier();
                    retorno.setId(rs.getInt("SUPPLIERID"));
                    retorno.setName(rs.getString("SUPPLIERNAME"));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e.getMessage());
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                stmt.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
        }

        return retorno;
    }
}
